using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace MacViet.DataBaseAPI
{
    /// <summary>
    /// Description: Concrete DataProvider for table CompanyInfo. Provides atomic API functions.
    /// Author:  phong.huynh
    /// Created: 13/08/2010 8:44:01 SA
    /// </summary>
    public class PRODUCTSDataAPI
    {
        private SQLHelper _dbTool = new SQLHelper();



        // ************************************
        // Implements function getByColumnName
        // ************************************


        //HUYNQ view product---------------
        public DataTable getPRODUCTS_GetPRO_NAME_By_Pro_ACC(System.Int32 PROTYP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PROTYP_ID, PROTYP_ID),
                                    new SqlParameter("@ACC_ID", ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_GetPRO_NAME_By_Pro_ACC", parm);
        }
        //-----------------------



        public DataTable getPRODUCTS_GetPRO_QUANTITY_By_Pro_ACC(System.Int32 PROTYP_ID, System.Int32 PRO_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PROTYP_ID, PROTYP_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID),
                                    new SqlParameter("@ACC_ID", ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_GetPRO_QUANTITY_By_Pro_ACC", parm);


        }
        public DataTable getByPRO_ACCID(System.Int32 PROTYP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PROTYP_ID, PROTYP_ID),
                                    new SqlParameter("@ACC_ID", ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_GetPRO_NAME_By_Pro_ACC", parm);
        }

        public DataTable getByPRO_QTTY(System.Int32 PRO_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID),
                                    new SqlParameter("@ACC_ID", ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_GetPRO_QUANTITY_By_Pro_ACC1", parm);
        }

        public DataTable getByPRO_QTTY_INVENTORY(System.Int32 PROTYP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PROTYP_ID, PROTYP_ID),
                                    new SqlParameter("@ACC_ID", ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_GetPRO_NAME_By_Pro_ACC_INVENTORTY", parm);
        }

        /*--------------------------end HUYNQ -----------------*/
        public DataTable getByPRO_ID(System.Int32 PRO_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPRO_ID", parm);
        }
        public DataTable getTableTempt(System.Int32 PRO_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };
            return _dbTool.executeReader("sp_getBillTempt", parm);
        }
        public DataTable getByPROTYP_ID(System.Int32 PROTYP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PROTYP_ID, PROTYP_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPROTYP_ID", parm);
        }


        // USe

        //---------------------------------------------------
        public DataTable getProName_ByPROTYP_ID(System.Int32 PROTYP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(PRODUCTSDTO._PROTYP_ID, PROTYP_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_GetPRO_NAME_By_ProTyp", parm);
        }

        //---------------------------------------------------




        public DataTable getByPRO_NAME(System.String PRO_NAME)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_NAME, PRO_NAME) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPRO_NAME", parm);

        }


        public DataTable getByPRO_DESCRIPTION(System.String PRO_DESCRIPTION)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_DESCRIPTION, PRO_DESCRIPTION) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPRO_DESCRIPTION", parm);
        }

        public DataTable Get_DESCRIPTION(System.Int32 PRO_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };
            return _dbTool.executeReader("SP_GET_DES_BY_PRODUCT_ID", parm);
        }

        public DataTable Get_More(System.Int32 PRO_DETAIL_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(PRODUCTSDTO._PRO_DETAIL_ID, PRO_DETAIL_ID) };
            return _dbTool.executeReader("SP_GET_ALL_BY_PRODUCT_DETAIL_ID", parm);
        }

        public DataTable Get_PRODUCT_DETAIL_BY_PRO_ID(System.Int32 PRO_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };
            return _dbTool.executeReader("SP_GET_PRO_DETAIL_BY_PRODUCT_ID", parm);
        }
        public DataTable Get_PRODUCT_DETAIL_QUANTITY_BY_ID(System.Int32 PRO_DETAIL_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(PRODUCTSDTO._PRO_DETAIL_ID, PRO_DETAIL_ID) };
            return _dbTool.executeReader("SP_GET_QUANTITY_BY_PRODUCT_DETAIL_ID", parm);
        }

        


        public DataTable getByPRO_PRICE(System.Decimal PRO_PRICE)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_PRICE, PRO_PRICE) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPRO_PRICE", parm);
        }

        public DataTable getByPRO_QUANTITY(System.Int32 PRO_QUANTITY,string id)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_QUANTITY, PRO_QUANTITY) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPRO_QUANTITY", parm);
        }

        public DataTable getcartAmount(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_CART_GET_AMOUNT", parm);

        }

        public DataTable CartTotal(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_CART_TOTAL", parm);

        }

        //---------------------------------------------------


        public DataSet getPro_and_ProTyp(System.Int32 PROTYP_ID, System.Int32 PRO_ID)
        {
            SqlParameter[] parm ={ new SqlParameter(PRODUCTSDTO._PROTYP_ID,PROTYP_ID),
                                  new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID)};
            return _dbTool.GetDataSet("SP_SEL_PRODUCTS_GetPRO_ID_AND_PROTYP_ID", parm);


        }

    
        //---------------------------------------------------




        // *************************************************
        // Implement function getAll/getByDynamicCondition
        // *************************************************

        //public DataTable get_Products_Pricelist()
        //{
        //    return _dbTool.executeReader("SP_SET_PRODUCTS_GetPRO_PRICELIST");
        //}
        public DataTable get_Products_Edit(System.Int32 ACC_ID)
        {
            SqlParameter[] parm ={new SqlParameter(PRODUCTSDTO._ACC_ID,ACC_ID)};
            return _dbTool.executeReader("SP_SET_PRODUCTS_GetEditProduct",parm);
        }

        public DataTable get_Products_Details_Edit(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SET_PRODUCTS_Details_GetEditProduct", parm);
        }



        public DataTable getAll(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getAll", parm);
        }
        public DataTable getAll_Details(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_DETAILS_getAll", parm);
        }

        public DataTable get_Product_Type(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCT_TYPE_getAll", parm);
        }
       
          public DataTable getcartAll()
        {
            return _dbTool.executeReader("SP_CART_GET_ALL");
         
        }


        // ********************************************************
        // Implement function getByPrimaryKeys / getByForeignKeys
        // In case table have composite primary/foreign keys
        // ********************************************************

        public DataTable getByPrimaryKeys(System.Int32 PRO_ID,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };
            return _dbTool.executeReader("SP_SEL_PRODUCTS_getByPrimaryKeys", parm);
        }


        //-------------------------
         public DataTable getPrice(System.Int32 PRO_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };
            return _dbTool.executeReader("SP_SEL_GET_PRICE_BY_PRO_ID", parm);
        }


         public DataTable getSpace(System.Int32 PRO_ID)
         {
            
             return _dbTool.executeReader("SP_SEL_SPACE_GET");
         }
         //-------------------------



         public void  insertPRODUCTS(System.Int32 PROTYP_ID, System.Int32 ACC_ID, System.String PRO_NAME, System.String PRO_DESCRIPTION, System.Decimal PRO_PRICE, System.Int32 PRO_QUANTITY)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID,1),
                                    new SqlParameter(PRODUCTSDTO._PROTYP_ID,PROTYP_ID),
                                    new SqlParameter(PRODUCT_TYPESDTO._ACC_ID,ACC_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_NAME,PRO_NAME),
                                    new SqlParameter(PRODUCTSDTO._PRO_DESCRIPTION,PRO_DESCRIPTION),
                                    new SqlParameter(PRODUCTSDTO._PRO_PRICE,PRO_PRICE),
                                    new SqlParameter(PRODUCTSDTO._PRO_QUANTITY,PRO_QUANTITY)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_PRODUCTS_values", parm);

        }
         public void insertPRODUCTDetails(System.Int32 PRO_ID, System.Int32 ACC_ID, System.String PRO_DETAIL_NAME, System.String PRO_DETAIL_DESCRIPTION, System.Decimal PRO_DETAIL_PRICE, System.Int32 PRO_DETAIL_QUANTITY)
         {
             SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID),
                                    new SqlParameter(PRODUCT_TYPESDTO._ACC_ID,ACC_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_NAME,PRO_DETAIL_NAME),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_DESCRIPTION,PRO_DETAIL_DESCRIPTION),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_PRICE,PRO_DETAIL_PRICE),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_QUANTITY,PRO_DETAIL_QUANTITY)};
             int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_PRODUCTS_DETAILS_values", parm);

         }



      public bool GoIntoCart(System.Int32 PRO_ID, System.String PRO_NAME, System.String PRO_DESCRIPTION, float PRO_PRICE, System.Int32 PRO_QUANTITY,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_NAME,PRO_NAME),
                                    new SqlParameter(PRODUCTSDTO._PRO_DESCRIPTION,PRO_DESCRIPTION),
                                    new SqlParameter(PRODUCTSDTO._PRO_PRICE,PRO_PRICE),
                                    new SqlParameter(PRODUCTSDTO._PRO_QUANTITY,PRO_QUANTITY)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_CART", parm);




            return (effectedRows == 1);
        }



        ///<summary>		
        /// Thu+.c hie^.n ca^u le^.nh update du+.a tre^n kho'a chi'nh
        ///</summary>


<<<<<<< .mine
        //    return affectRows == 1;
        //}
        //dfdfdfdfdfd/

        public void updatePRODUCTS(System.Int32 PRO_ID, System.Int32 PROTYP_ID, System.String PRO_NAME, System.String PRO_DESCRIPTION, System.Decimal PRO_PRICE, System.Int32 PRO_QUANTITY,System.Int32 ACC_ID)
=======
        public void updatePRODUCTS(System.Int32 PRO_ID, System.Int32 PROTYP_ID, System.String PRO_NAME, System.String PRO_DESCRIPTION, System.Decimal PRO_PRICE, System.Int32 PRO_QUANTITY,System.Int32 ACC_ID)
>>>>>>> .r340
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID),
                                    new SqlParameter(PRODUCTSDTO._PROTYP_ID,PROTYP_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_NAME,PRO_NAME),
                                    new SqlParameter(PRODUCTSDTO._PRO_DESCRIPTION,PRO_DESCRIPTION),
                                    new SqlParameter(PRODUCTSDTO._PRO_PRICE,PRO_PRICE),
<<<<<<< .mine
                                    new SqlParameter(PRODUCTSDTO._PRO_QUANTITY,PRO_QUANTITY),
                                  new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID)};
=======
                                    new SqlParameter(PRODUCTSDTO._PRO_QUANTITY,PRO_QUANTITY),
                                   new SqlParameter(PRODUCT_TYPESDTO._ACC_ID,ACC_ID)};
>>>>>>> .r340
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_PRODUCTS_update", parm);
        }

        public void updatePRODUCT_DETAILS(System.Int32 PRO_DETAIL_ID, System.Int32 PRO_ID, System.String PRO_DETAIL_NAME, System.String PRO_DETAIL_DESCRIPTION, System.Decimal PRO_DETAIL_PRICE, System.Int32 PRO_DETAIL_QUANTITY, System.Int32 ACC_ID)
        {


            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_DETAIL_ID,PRO_DETAIL_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID),                                  
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_NAME,PRO_DETAIL_NAME),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_DESCRIPTION,PRO_DETAIL_DESCRIPTION),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_PRICE,PRO_DETAIL_PRICE),
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_QUANTITY,PRO_DETAIL_QUANTITY),
                                  new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_PRODUCTS_DETAILS_update", parm);
        }


        public void updatePro_two(System.Int32 PRO_ID,System.Int32 PRO_QUANTITY)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID),                                    
                                    new SqlParameter(PRODUCTSDTO._PRO_QUANTITY,PRO_QUANTITY)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_PRODUCTS_Two", parm);
        }



        public void updateProDetails_two(System.Int32 PRO_DETAIL_ID, System.Int32 PRO_DETAIL_QUANTITY)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_DETAIL_ID,PRO_DETAIL_ID),                                    
                                    new SqlParameter(PRODUCTSDTO._PRO_DETAIL_QUANTITY,PRO_DETAIL_QUANTITY)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_PRODUCTS_DETAILS_Two", parm);
        }
        ///<summary>
        /// Kie^?m tra trong Category
        /// Du+. tre^n kho'a chi'nh cu?a ddo^'i tu+o+.ng DTO ddu+o+.c truye^`n va`o:
        ///		+ Ti`m tha^'y ddo^'i tu+o+.ng tru`ng kho'a -> ca^.p nha^.t (update)
        ///		+ Kho^ng ti`m tha^'y -> the^m mo+'i (insert)
        ///</summary>		
        ///<returns>True la` tha`nh co^ng</returns>
        public bool insertOrUpdatePRODUCTS(ref System.Int32 PRO_ID, System.Int32 PROTYP_ID, System.String PRO_NAME, System.String PRO_DESCRIPTION, System.Decimal PRO_PRICE, System.Int32 PRO_QUANTITY)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID,PRO_ID),
                                    new SqlParameter(PRODUCTSDTO._PRO_NAME,PRO_NAME),
                                    new SqlParameter(PRODUCTSDTO._PRO_DESCRIPTION,PRO_DESCRIPTION),
                                    new SqlParameter(PRODUCTSDTO._PRO_PRICE,PRO_PRICE),
                                    new SqlParameter(PRODUCTSDTO._PRO_QUANTITY,PRO_QUANTITY)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_IU_PRODUCTS_insertOrUpdate", parm);


            return effectedRows == 1;
        }


        // ********************************************************************************
        // Implementation of deleteAll/deteleByForeignKeys/deleteByPrimaryKeys function		
        // ********************************************************************************

        // return number of deleted rows		
        public int deleteAll()
        {
            return _dbTool.ExecuteNoneQuery("SP_DEL_PRODUCTS_deleteAll");
        }

        // return number of deleted rows
        public int deleteByPrimaryKeys(System.Int32 PRO_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_ID, PRO_ID) };            
            int affectRows = _dbTool.ExecuteNoneQuery("SP_DEL_PRODUCTS_deleteByPrimaryKeys", parm);
            return affectRows;
        }
        public int deleteByProDetails(System.Int32 PRO_DETAIL_ID)
        {
            SqlParameter[] parm = { new SqlParameter(PRODUCTSDTO._PRO_DETAIL_ID, PRO_DETAIL_ID) };
            int affectRows = _dbTool.ExecuteNoneQuery("SP_DEL_PRODUCTS_deleteByProDetails", parm);
            return affectRows;
        }

          public int deleteFromCart(System.Int32 Car_ID)
        {
            SqlParameter[] parm = { new SqlParameter("@Cart_ID", Car_ID) };
            return _dbTool.ExecuteNoneQuery("SP_DELETE_FROM_CART", parm);
        }



        

    }
}